I would like to query an on-premises SQL Server from an Azure SQL Database. This answer seems to indicate that I can do just that with the "Elastic Database Query" feature.
However, everything I have read about "Elastic Database Query" only mentions connecting to another Azure SQL Database, not an on-premises SQL Server database.
I tried using the "Elastic Database Query" functionality by running these statements one after the other:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MY_SUPER_SECRET_PASSWORD';
CREATE DATABASE SCOPED CREDENTIAL SSDB_On_Premises_Credentials
WITH IDENTITY = 'MY_USERNAME',
SECRET = 'MY_OTHER_SUPER_SECRET_PASSWORD';
CREATE EXTERNAL DATA SOURCE SSDB_On_Premises WITH
(TYPE = RDBMS,
LOCATION = 'the.path.to.my.on.premises.database',
DATABASE_NAME = 'MyDatabaseName',
CREDENTIAL = SSDB_On_Premises_Credentials
) ;
CREATE EXTERNAL TABLE USER(
ID INT NOT NULL,
FIRSTNAME NVarchar(255),
LASTNAME NVarchar(255) NOT NULL
)
WITH
(
DATA_SOURCE = SSDB_On_Premises
);
SELECT * FROM USER;
However, I get this error:
A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL
Server)
This might be a problem with the configuration settings on my on-premises SQL Server, but I'm guessing its more likely that "Elastic Database Query" just doesn't connect to an on-premises data source.